#!/use/bin/python
# coding=utf-8
# 主动服务-开机数/设备数-同比/环比 analysis_startup_number
# type,1为开机次数 2为开机设备量
import datetime
import pymysql
from dateutil.relativedelta import relativedelta

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#通过通知日志项 key 跟 日期获取设备数量
# isDistinct 是否去重 1 是 0否
# key 通用日志项的key值 如:'tipTaskStart' 早间提醒页面准备启动
# date 日期 如:"2022-08-25"
# msgDict 日用日志项中 msg 的过滤条件: 如 {'result': 1}
def getNumByLogKeyAndDate(isDistinct,key,date,msgDict):
    conn, cur = getDataConnection()
    if isDistinct == 1:
        selectNumSql = f"""
                          select count(DISTINCT(t.active_id)) from log_common_service t where t.`key` = '{key}'   
                          """ ;
    else:
        selectNumSql = f"""
                          select count(1) from log_common_service t where t.`key` = '{key}'   
                          """;

    if len(date) >0:
        selectNumSql = f"""{selectNumSql} and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}' """

    msgWhere = ""
    #对日志表 中msg 数据进行过滤 {'result': 1}
    if msgDict:
        for i, j in msgDict.items():
            #判断value是否是数值
            if isinstance(j, int):
                msgWhere = f""" and JSON_EXTRACT(msg,"$.{i}") ={j}"""
            else:
                msgWhere = f""" and JSON_EXTRACT(msg,"$.{i}") ='{j}'"""

    cur.execute(selectNumSql+msgWhere)
    #print(selectNumSql+msgWhere);
    numResult = cur.fetchone()
    num = 0
    if numResult != None:
        num = numResult[0]
    return num

#根据月份 获取本月指定指标的 总和
#tableName 指定的报表
#columnName 指定的指标字段
#date 月份
# sqlDict 附加 的过滤条件: 如 {'type': 1}
def getSumNumByTableColumnNameAndDate(tableName,columnName,date,sqlDict):
    conn, cur = getDataConnection()
    selectNumSql = f"""
                     select sum({columnName}) from {tableName} t where 1=1   
                     """;
    if len(date) >0:
        selectNumSql = f"""{selectNumSql} and SUBSTR( t.ref_date, 1,7) = '{date}' and LENGTH(t.ref_date) = 10  """
    sqlWhere = ""
    #对日志表 中msg 数据进行过滤 {'result': 1}
    if sqlDict:
        for i, j in sqlDict.items():
            #判断value是否是数值
            if isinstance(j, int):
                sqlWhere = f""" and {i} ={j}"""
            else:
                sqlWhere = f""" and {i} ='{j}'"""
    cur.execute(selectNumSql+sqlWhere)
    #print(selectNumSql+sqlWhere);
    numResult = cur.fetchone()
    num = 0
    if numResult!= None:
        num = numResult[0]
    return num

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql):
    conn, cur = getDataConnection()
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num
    return countNum

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if Y != 0:
        rate = round(X / Y, 4) * 100
    return rate

# 记录 数据分析-主动服务-开机数-同比/环比  analysis_startup_number   开机次数
def insertStartupNumberTyep1(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_startup_number t where 1=1 and t.ref_date =  '{date}' and type = 1")
        #获取 当日总体开机次数
        allNum = selectNumBySql(
            f"select count(1) from log_sys_poweron t where  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}'")
        # 获取当日早间开机 早上启动播报设备量
        msgDict = {'result': 1}
        zjkjNum = getNumByLogKeyAndDate(0, 'tipTaskStart', date, msgDict)
        # 获取 追剧提醒开机次数
        zjtxNum = selectNumBySql(
            f"select count(1) from log_zjtx_page_show t  where  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}' and result = 1")
        # 计算因生活助手开机数
        serviceNum = zjkjNum + zjtxNum;

        if countNum == 0:
            logDetail = f"新增-{date}日开机数-同比/环比,总体开机数为{allNum},早间开机数为{zjkjNum},追剧提醒开机数为{zjtxNum},因生活助手开机数为{serviceNum}"
            print(logDetail)
            # 插入 设备开机次数
            insertSql = f"""insert into analysis_startup_number(startup_num,zjkj_startup_num,zjtx_startup_num,service_startup_num,ref_date,type) 
                value({allNum},{zjkjNum},{zjtxNum},{serviceNum},'{date}',1)"""
            cur.execute(insertSql)
        else:
            logDetail = f"更新-{date}日开机数-同比/环比,总体开机数为{allNum},早间开机数为{zjkjNum},追剧提醒开机数为{zjtxNum},因生活助手开机数为{serviceNum}"
            print(logDetail)
            # 更新 设备开机次数
            updateSql = f"""
                update analysis_startup_number set startup_num = {allNum} ,zjkj_startup_num = {zjkjNum},zjtx_startup_num = {zjtxNum},service_startup_num = {serviceNum} ,update_time = now()  where
                    ref_date = '{date}' and type = 1
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 数据分析-主动服务-开机数-同比/环比  analysis_startup_number   开机设备数
def insertStartupNumberTyep2(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_startup_number t where 1=1 and t.ref_date =  '{date}' and type = 2")
        #获取 当日总体开机次数
        allNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_sys_poweron t where  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}'")
        #获取 早间开机次数
        # 获取当日早间开机 早上启动播报设备量
        msgDict = {'result': 1}
        zjkjNum = getNumByLogKeyAndDate(1, 'tipTaskStart', date, msgDict)
        # 获取 追剧提醒开机次数
        zjtxNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id))  from log_zjtx_page_show t  where  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}' and result = 1")
        # 计算因生活助手开机设备量
        serviceNum = zjkjNum + zjtxNum;

        if countNum == 0:
            logDetail = f"新增-{date}日开机设备量-同比/环比,总体开机设备量为{allNum},早间开机设备量为{zjkjNum},追剧提醒开机设备量为{zjtxNum},因生活助手开机设备量为{serviceNum}"
            print(logDetail)
            # 插入 设备开机设备量
            insertSql = f"""insert into analysis_startup_number(startup_num,zjkj_startup_num,zjtx_startup_num,service_startup_num,ref_date,type) 
                value({allNum},{zjkjNum},{zjtxNum},{serviceNum},'{date}',2)"""
            cur.execute(insertSql)
        else:
            logDetail = f"更新-{date}日开机设备量-同比/环比,总体开机设备量为{allNum},早间开机设备量为{zjkjNum},追剧提醒开机设备量为{zjtxNum},因生活助手开机设备量为{serviceNum}"
            print(logDetail)
            # 更新 设备开机设备量
            updateSql = f"""
                update analysis_startup_number set startup_num = {allNum} ,zjkj_startup_num = {zjkjNum},zjtx_startup_num = {zjtxNum},service_startup_num = {serviceNum} ,update_time = now()  where
                    ref_date = '{date}' and type = 2
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 数据分析-主动服务-开机数-同比/环比  analysis_startup_number   开机次数 -月度
def insertStartupNumberMonthTyep1(toMonthDay,beforeMonthDay,beforeYearDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_startup_number t where 1=1 and t.ref_date =  '{toMonthDay}' and type = 1")
        # 获取 月度总体开机次数
        sqlDict = {'type': 1}
        allNum = getSumNumByTableColumnNameAndDate('analysis_startup_number', 'startup_num', toMonthDay,sqlDict)
        # 获取 月度早间开机次数
        zjkjNum = getSumNumByTableColumnNameAndDate('analysis_startup_number', 'zjkj_startup_num', toMonthDay,sqlDict)
        # 获取 月度追剧提醒开机次数
        zjtxNum = getSumNumByTableColumnNameAndDate('analysis_startup_number', 'zjtx_startup_num', toMonthDay,sqlDict)
        # 获取 月度因生活助手开机次数
        serviceNum = getSumNumByTableColumnNameAndDate('analysis_startup_number', 'service_startup_num', toMonthDay, sqlDict)
        # 获取 上一个月月总体开机次数
        bmAllNum = selectNumBySql(
            f"select startup_num from analysis_startup_number t  where  t.ref_date = '{beforeMonthDay}' and type =1 ")
        #上一个年相同月份 计算环比
        byAllNum = selectNumBySql(
            f"select startup_num from analysis_startup_number t  where  t.ref_date = '{beforeYearDay}' and type =1")
        # 计算开机设备数及同比环比
        monthOnMonthAll = getRateByXY(allNum-bmAllNum,bmAllNum)
        yearOnYearAll = getRateByXY(allNum-byAllNum,byAllNum)

        # 获取 上一个月月因生活助手开机次数
        bmServiceNum = selectNumBySql(
            f"select service_startup_num from analysis_startup_number t  where  t.ref_date = '{beforeMonthDay}' and type =1 ")
        # 上一个年相同月份 因生活助手开机次数 计算环比
        byServiceNum = selectNumBySql(
            f"select service_startup_num from analysis_startup_number t  where  t.ref_date = '{beforeYearDay}' and type =1")
        # 计算因主动服务 开机设备数及同比环比
        monthOnMonthService = getRateByXY(serviceNum - bmServiceNum, bmServiceNum)
        yearOnYearService = getRateByXY(serviceNum - byServiceNum, byServiceNum)


        if countNum == 0:
            logDetail = f"新增-{toMonthDay}月开机数-同比/环比,总体开机数为{allNum},早间开机数为{zjkjNum},追剧提醒开机数为{zjtxNum},因生活助手开机数为{serviceNum},总体开机上一个月开机数为{bmAllNum},同比为{monthOnMonthAll}," \
                        f"总体开机上一个年今月开机数为{byAllNum},环比为{yearOnYearAll},因生活助手开机上一个月开机数为{bmServiceNum},同比为{monthOnMonthService},因生活助手开机上一年今月开机数为{byServiceNum},环比为{yearOnYearService}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_startup_number(startup_num,zjkj_startup_num,zjtx_startup_num,service_startup_num,month_on_month,year_on_year,service_month_on_month,service_year_on_year,ref_date,type) 
                value({allNum},{zjkjNum},{zjtxNum},{serviceNum},{monthOnMonthAll},{yearOnYearAll},{monthOnMonthService},{yearOnYearService},'{toMonthDay}',1)"""
            cur.execute(insertSql)
        else:
            logDetail = f"更新-{toMonthDay}月开机数-同比/环比,总体开机数为{allNum},早间开机数为{zjkjNum},追剧提醒开机数为{zjtxNum},因生活助手开机数为{serviceNum},总体开机上一个月开机数为{bmAllNum},同比为{monthOnMonthAll}," \
                        f"总体开机上一个年今月开机数为{byAllNum},环比为{yearOnYearAll},因生活助手开机上一个月开机数为{bmServiceNum},同比为{monthOnMonthService},因生活助手开机上一年今月开机数为{byServiceNum},环比为{yearOnYearService}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_startup_number set startup_num = {allNum} ,zjkj_startup_num = {zjkjNum},zjtx_startup_num = {zjtxNum}, service_startup_num={serviceNum},month_on_month = {monthOnMonthAll},year_on_year = {yearOnYearAll},
                service_year_on_year = {yearOnYearService},service_month_on_month = {monthOnMonthService},update_time = now()  where ref_date = '{toMonthDay}' and type = 1
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 数据分析-主动服务-开机数-同比/环比  analysis_startup_number   开机设备数 -月度
def insertStartupNumberMonthTyep2(toMonthDay,beforeMonthDay,beforeYearDay):
    try:
        conn, cur = getDataConnection()
        # 判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_startup_number t where 1=1 and t.ref_date =  '{toMonthDay}' and type = 2")
        # 获取 月度总体开机次数
        sqlDict = {'type': 2}
        allNum = getSumNumByTableColumnNameAndDate('analysis_startup_number', 'startup_num', toMonthDay, sqlDict)
        # 获取 月度早间开机次数
        zjkjNum = getSumNumByTableColumnNameAndDate('analysis_startup_number', 'zjkj_startup_num', toMonthDay, sqlDict)
        # 获取 月度追剧提醒开机次数
        zjtxNum = getSumNumByTableColumnNameAndDate('analysis_startup_number', 'zjtx_startup_num', toMonthDay, sqlDict)
        # 获取 月度因生活助手开机次数
        serviceNum = getSumNumByTableColumnNameAndDate('analysis_startup_number', 'service_startup_num', toMonthDay,
                                                       sqlDict)
        # 获取 上一个月月总体开机设备量
        bmAllNum = selectNumBySql(
            f"select startup_num from analysis_startup_number t  where  t.ref_date = '{beforeMonthDay}' and type =2 ")
        # 上一个年相同月份 计算环比
        byAllNum = selectNumBySql(
            f"select startup_num from analysis_startup_number t  where  t.ref_date = '{beforeYearDay}' and type =2")
        # 计算开机设备数及同比环比
        monthOnMonthAll = getRateByXY(allNum - bmAllNum, bmAllNum)
        yearOnYearAll = getRateByXY(allNum - byAllNum, byAllNum)

        # 获取 上一个月月因生活助手开机设备量
        bmServiceNum = selectNumBySql(
            f"select service_startup_num from analysis_startup_number t  where  t.ref_date = '{beforeMonthDay}' and type =2 ")
        # 上一个年相同月份 因生活助手开机次数 计算环比
        byServiceNum = selectNumBySql(
            f"select service_startup_num from analysis_startup_number t  where  t.ref_date = '{beforeYearDay}' and type =2")
        # 计算因主动服务 开机设备数及同比环比
        monthOnMonthService = getRateByXY(serviceNum - bmServiceNum, bmServiceNum)
        yearOnYearService = getRateByXY(serviceNum - byServiceNum, byServiceNum)

        if countNum == 0:
            logDetail = f"新增-{toMonthDay}月开机设备量-同比/环比,总体开机设备量为{allNum},早间开机设备量为{zjkjNum},追剧提醒开机设备量为{zjtxNum},因生活助手开机设备量为{serviceNum},总体开机上一个月开机设备量为{bmAllNum},同比为{monthOnMonthAll}," \
                        f"总体开机上一个年今月开机设备量为{byAllNum},环比为{yearOnYearAll},因生活助手开机上一个月开机设备量为{bmServiceNum},同比为{monthOnMonthService},因生活助手开机上一年今月开机设备量为{byServiceNum},环比为{yearOnYearService}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_startup_number(startup_num,zjkj_startup_num,zjtx_startup_num,service_startup_num,month_on_month,year_on_year,service_month_on_month,service_year_on_year,ref_date,type) 
                value({allNum},{zjkjNum},{zjtxNum},{serviceNum},{monthOnMonthAll},{yearOnYearAll},{monthOnMonthService},{yearOnYearService},'{toMonthDay}',2)"""
            cur.execute(insertSql)
        else:
            logDetail = f"更新-{toMonthDay}月开机设备量-同比/环比,总体开机设备量为{allNum},早间开机设备量为{zjkjNum},追剧提醒开机设备量为{zjtxNum},因生活助手开机设备量为{serviceNum},总体开机上一个月开机设备量为{bmAllNum},同比为{monthOnMonthAll}," \
                        f"总体开机上一个年今月开机设备量为{byAllNum},环比为{yearOnYearAll},因生活助手开机上一个月开机设备量为{bmServiceNum},同比为{monthOnMonthService},因生活助手开机上一年今月开机设备量为{byServiceNum},环比为{yearOnYearService}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_startup_number set startup_num = {allNum} ,zjkj_startup_num = {zjkjNum},zjtx_startup_num = {zjtxNum}, service_startup_num={serviceNum},month_on_month = {monthOnMonthAll},year_on_year = {yearOnYearAll},
                service_year_on_year = {yearOnYearService},service_month_on_month = {monthOnMonthService},update_time = now()  where ref_date = '{toMonthDay}' and type = 2
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

# 获取上一个月
def getBeforeMonth(d):
    today = datetime.datetime.now()
    re_date = (today + relativedelta(months=-d)).strftime("%Y-%m")
    return re_date

# 获取上一个年当月
def getBeforeYear():
    today = datetime.datetime.now()
    re_date = (today + relativedelta(years=-1)).strftime("%Y-%m")
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 52
        jobName = '核心指标-生活助手-开机数/设备数及同比/环比	'
        tableName = 'analysis_startup_number'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    toMonthDay = getMonthdate(d)
    beforeMonthDay = getBeforeMonth(2)
    beforeYearDay = getBeforeYear()
    print("%s 日期,执行<开机数/设备数-同比/环比>报表" % date)
    insertStartupNumberTyep1(date)
    insertStartupNumberTyep2(date)
    insertStartupNumberMonthTyep1(toMonthDay,beforeMonthDay,beforeYearDay)
    insertStartupNumberMonthTyep2(toMonthDay, beforeMonthDay, beforeYearDay)


